import math
import smtplib
import sys
from email.header import Header
from email.mime.text import MIMEText

import psycopg2


class AutoSendEmail:
    def __init__(self, db):
        self.db = db
        self.v_rows = []
        self.table_rows = ''
        self.func_master_prefix = {'FU_AC': ['接入层测试', 5],
                                   'FU_SQ': ['SQL层测试', 14],
                                   'FU_ST': ['存储层测试', 12],
                                   'DF_MA': ['管理测试', 13],
                                   'DF_SE': ['数据安全测试', 12],
                                   'DF_HA': ['高可用能力', 27],
                                   'EC_MY': ['mysql兼容', 2]}
        self.func_master_dict = {
            'FU_AC_JDBC_PY': ['', ''],
            'FU_AC_ODBC_PY': ['', ''],
            'FU_AC_GDBC_PY': ['', ''],
            'FU_AC_PDBC_PY': ['', ''],
            'FU_AC_PLUGINS_PY': ['', ''],
            'FU_SQ_GRAM_PY_1': ['', ''],
            'FU_SQ_GRAM_PY_2': ['', ''],
            'FU_SQ_GRAM_PY_3': ['', ''],
            'FU_SQ_GRAM_PY_4': ['', ''],
            'FU_SQ_GRAM_SQL': ['', ''],
            'FU_SQ_PROC_PY': ['', ''],
            'FU_SQ_PROC_SQL': ['', ''],
            'FU_SQ_SYS_PY': ['', ''],
            'FU_SQ_PERF_PY_1': ['', ''],
            'FU_SQ_PERF_PY_2': ['', ''],
            'FU_SQ_KEYWORD_PY': ['', ''],
            'FU_SQ_KEYWORD_SQL_1': ['', ''],
            'FU_SQ_KEYWORD_SQL_2': ['', ''],
            'FU_SQ_PARALLEL_QUERY_PY': ['', ''],
            'FU_ST_INDEX_PY': ['', ''],
            'FU_ST_INDEX_SQL': ['', ''],
            'FU_ST_PART_PY': ['', ''],
            'FU_ST_PART_SQL': ['', ''],
            'FU_ST_MOT_PY': ['', ''],
            'FU_ST_MATER_SQL': ['', ''],
            'FU_ST_FULLINDEX_PY': ['', ''],
            'FU_ST_FULLINDEX_SQL': ['', ''],
            'FU_ST_FLASHBACK_SQL': ['', ''],
            'FU_ST_USTORE_SQL_0': ['-', ''],
            'FU_ST_USTORE_SQL_1': ['-', ''],
            'FU_ST_USTORE_SQL_2': ['-', ''],
            'FU_ST_USTORE_SQL_3': ['-', ''],
            'FU_ST_USTORE_SQL_4': ['-', ''],
            'FU_ST_ROWCOMPRESS_SQL': ['', ''],
            'DF_MA_DROPNODE_PY_1': ['', ''],
            'DF_MA_DROPNODE_PY_2': ['', ''],
            'DF_MA_UPGRADE_PY': ['', ''],
            'DF_MA_TOOLS_PY_1': ['', ''],
            'DF_MA_TOOLS_PY_2': ['', ''],
            'DF_MA_TOOLS_PY_3': ['', ''],
            'DF_MA_TOOLS_PY_4': ['', ''],
            'DF_MA_TOOLS_PY_5': ['', ''],
            'DF_MA_TOOLS_PY_6': ['', ''],
            'DF_MA_TOOLS_PY_7': ['', ''],
            'DF_MA_TOOLS_PY_8': ['', ''],
            'DF_MA_OM_PY': ['', ''],
            'DF_MA_OM_SQL': ['', ''],
            'DF_SE_IDENTITY_PY': ['', ''],
            'DF_SE_VISIT_PY': ['', ''],
            'DF_SE_VISIT_SQL': ['', ''],
            'DF_SE_THREE_PY_1': ['', ''],
            'DF_SE_THREE_PY_2': ['', ''],
            'DF_SE_AUDIT_PY': ['', ''],
            'DF_SE_AUDIT_SQL': ['', ''],
            'DF_SE_ENCRYPT_PY': ['', ''],
            'DF_SE_MASK_PY': ['', ''],
            'DF_SE_EQUALITY_PY': ['', ''],
            'DF_SE_MODIFY_SQL': ['', ''],
            'DF_SE_MODIFY_PY': ['', ''],
            'DF_HA_BACKUP_PY_1': ['', ''],
            'DF_HA_BACKUP_PY_2': ['', ''],
            'DF_HA_BACKUP_PY_3': ['', ''],
            'DF_HA_DELAYBACK_PY_1': ['', ''],
            'DF_HA_DELAYBACK_PY_2': ['', ''],
            'DF_HA_DUMP_PY': ['', ''],
            'DF_HA_COPY_PY': ['', ''],
            'DF_HA_GUC_PY_1': ['', ''],
            'DF_HA_GUC_PY_2': ['', ''],
            'DF_HA_GUC_PY_3': ['', ''],
            'DF_HA_GUC_PY_4': ['', ''],
            'DF_HA_GUC_PY_5': ['', ''],
            'DF_HA_GUC_PY_6': ['', ''],
            'DF_HA_GUC_PY_7': ['', ''],
            'DF_HA_GUC_PY_8': ['', ''],
            'DF_HA_GUC_PY_9': ['', ''],
            'DF_HA_GUC_PY_10': ['', ''],
            'DF_HA_GUC_PY_11': ['', ''],
            'DF_HA_GUC_PY_12': ['', ''],
            'DF_HA_GUC_PY_13': ['', ''],
            'DF_HA_GUC_SQL_1': ['', ''],
            'DF_HA_GUC_SQL_2': ['', ''],
            'DF_HA_GUC_SQL_3': ['', ''],
            'DF_HA_GUC_SQL_4': ['', ''],
            'DF_HA_PUB_SUB_PY_1': ['', ''],
            'DF_HA_PUB_SUB_PY_2': ['', ''],
            'DF_HA_GSC_PY': ['', ''],
            'EC_MY_PLUGIN_PY': ['', '甘洋 00625362'],
            'EC_MY_GRAMMAR_SQL': ['', '']}
        self.func_300_prefix = {'FU_AC': ['接入层测试', 5],
                                'FU_SQ': ['SQL层测试', 14],
                                'FU_ST': ['存储层测试', 12],
                                'DF_MA': ['管理测试', 13],
                                'DF_SE': ['数据安全测试', 12],
                                'DF_HA': ['高可用能力', 27]}
        self.func_300_dict = {
            'FU_AC_JDBC_PY': ['', ''],
            'FU_AC_ODBC_PY': ['', ''],
            'FU_AC_GDBC_PY': ['', ''],
            'FU_AC_PYTHON_PY': ['', ''],
            'FU_AC_PLUGINS_PY': ['', ''],
            'FU_SQ_GRAM_PY_1': ['', ''],
            'FU_SQ_GRAM_PY_2': ['', ''],
            'FU_SQ_GRAM_PY_3': ['', ''],
            'FU_SQ_GRAM_PY_4': ['', ''],
            'FU_SQ_GRAM_SQL': ['', ''],
            'FU_SQ_PROC_PY': ['', ''],
            'FU_SQ_PROC_SQL': ['', ''],
            'FU_SQ_SYS_PY': ['', ''],
            'FU_SQ_PERF_PY_1': ['', ''],
            'FU_SQ_PERF_PY_2': ['', ''],
            'FU_SQ_KEYWORD_PY': ['', ''],
            'FU_SQ_KEYWORD_SQL_1': ['', ''],
            'FU_SQ_KEYWORD_SQL_2': ['', ''],
            'FU_SQ_PARALLEL_QUERY_PY': ['', ''],
            'FU_ST_INDEX_PY': ['', ''],
            'FU_ST_INDEX_SQL': ['', ''],
            'FU_ST_PART_PY': ['', ''],
            'FU_ST_PART_SQL': ['', ''],
            'FU_ST_MOT_PY': ['', ''],
            'FU_ST_MATER_SQL': ['', ''],
            'FU_ST_FULLINDEX_PY': ['', ''],
            'FU_ST_FULLINDEX_SQL': ['', ''],
            'FU_ST_FLASHBACK_SQL': ['', ''],
            'FU_ST_USTORE_SQL_0': ['-', ''],
            'FU_ST_USTORE_SQL_1': ['-', ''],
            'FU_ST_USTORE_SQL_2': ['-', ''],
            'FU_ST_USTORE_SQL_3': ['-', ''],
            'FU_ST_USTORE_SQL_4': ['-', ''],
            'FU_ST_ROWCOMPRESS_SQL': ['', ''],
            'DF_MA_DROPNODE_PY_1': ['', ''],
            'DF_MA_DROPNODE_PY_2': ['', ''],
            'DF_MA_UPGRADE_PY': ['', ''],
            'DF_MA_TOOLS_PY_1': ['', ''],
            'DF_MA_TOOLS_PY_2': ['', ''],
            'DF_MA_TOOLS_PY_3': ['', ''],
            'DF_MA_TOOLS_PY_4': ['', ''],
            'DF_MA_TOOLS_PY_5': ['', ''],
            'DF_MA_TOOLS_PY_6': ['', ''],
            'DF_MA_TOOLS_PY_7': ['', ''],
            'DF_MA_TOOLS_PY_8': ['', ''],
            'DF_MA_OM_PY': ['', ''],
            'DF_MA_OM_SQL': ['', ''],
            'DF_SE_IDENTITY_PY': ['', ''],
            'DF_SE_VISIT_PY': ['', ''],
            'DF_SE_VISIT_SQL': ['', ''],
            'DF_SE_THREE_PY_1': ['', ''],
            'DF_SE_THREE_PY_2': ['', ''],
            'DF_SE_AUDIT_PY': ['', ''],
            'DF_SE_AUDIT_SQL': ['', ''],
            'DF_SE_ENCRYPT_PY': ['', ''],
            'DF_SE_MASK_PY': ['', ''],
            'DF_SE_EQUALITY_PY': ['', ''],
            'DF_SE_MODIFY_SQL': ['', ''],
            'DF_SE_MODIFY_PY': ['', ''],
            'DF_HA_BACKUP_PY_1': ['', ''],
            'DF_HA_BACKUP_PY_2': ['', ''],
            'DF_HA_BACKUP_PY_3': ['', ''],
            'DF_HA_DELAYBACK_PY_1': ['', ''],
            'DF_HA_DELAYBACK_PY_2': ['', ''],
            'DF_HA_DUMP_PY': ['', ''],
            'DF_HA_COPY_PY': ['', ''],
            'DF_HA_GUC_PY_1': ['', ''],
            'DF_HA_GUC_PY_2': ['', ''],
            'DF_HA_GUC_PY_3': ['', ''],
            'DF_HA_GUC_PY_4': ['', ''],
            'DF_HA_GUC_PY_5': ['', ''],
            'DF_HA_GUC_PY_6': ['', ''],
            'DF_HA_GUC_PY_7': ['', ''],
            'DF_HA_GUC_PY_8': ['', ''],
            'DF_HA_GUC_PY_9': ['', ''],
            'DF_HA_GUC_PY_10': ['', ''],
            'DF_HA_GUC_PY_11': ['', ''],
            'DF_HA_GUC_PY_12': ['', ''],
            'DF_HA_GUC_PY_13': ['', ''],
            'DF_HA_GUC_SQL_1': ['', ''],
            'DF_HA_GUC_SQL_2': ['', ''],
            'DF_HA_GUC_SQL_3': ['', ''],
            'DF_HA_GUC_SQL_4': ['', ''],
            'DF_HA_PUB_SUB_PY_1': ['', ''],
            'DF_HA_PUB_SUB_PY_2': ['', ''],
            'DF_HA_GSC_PY': ['', '']}
        self.func_200_prefix = {'DataType': ['数据类型', 1],
                                'MOT': ['MOT', 1],
                                'SQL': ['sql语法', 5],
                                'built': ['内建函数', 1],
                                'cast': ['类型转换', 1],
                                'expression': ['表达式', 1],
                                'foreign': ['外键', 1],
                                'full': ['全文索引', 1],
                                'gs_tools': ['工具', 1],
                                'guc': ['guc参数', 6],
                                'jdbc': ['接口兼容', 1],
                                'keyword': ['关键字', 1],
                                'logical': ['逻辑复制', 1],
                                'procedure': ['存储过程', 1],
                                'recovery': ['延迟回放', 1],
                                'security': ['安全', 1],
                                'server': ['服务端工具', 2],
                                'standby': ['备机逻辑复制', 1],
                                'system': ['系统表与系统视图', 3],
                                'tools': ['工具', 2],
                                'trigger': ['触发器', 1]}
        self.func_200_dict = {'DataType': ['*', ''],
                              'MOT': ['*', ''],
                              'SQL_DCL': ['*', ''],
                              'SQL_DDL': ['*', ''],
                              'SQL_DDL_GIN_INDEX': ['*', ''],
                              'SQL_DML': ['*', ''],
                              'SQL_DQL': ['*', ''],
                              'built_in_function': ['*', ''],
                              'cast': ['*', ''],
                              'expression': ['*', ''],
                              'foreign_key': ['*', ''],
                              'full_text_search': ['*', ''],
                              'gs_tools': ['*', ''],
                              'guc': ['*', ''],
                              'jdbc': ['*', ''],
                              'keyword': ['*', ''],
                              'logical_replication': ['*', ''],
                              'procedure': ['*', ''],
                              'recovery_delay': ['*', ''],
                              'security': ['*', ''],
                              'server_tools': ['*', ''],
                              'standby_logical_replication': ['*', ''],
                              'system_internal_tools': ['*', ''],
                              'system_table_view': ['*', ''],
                              'tools_gs_basebackup': ['*', ''],
                              'tools': ['*', ''],
                              'trigger': ['*', '']}

        self.relia_prefix_dict = {'DB_SYSTEM': ['数据库系统类', 1],
                                  'HARDWARE': ['硬件故障类', 2],
                                  'HUMAN': ['人为因素类', 1],
                                  'OS': ['操作系统类', 1],
                                  'PORT': ['端口故障类', 1],
                                  'RTO': ['极致RTO', 1],
                                  'TOOLS': ['工具类', 1],
                                  'ZLONGTIME': ['LONGTIME', 1]}
        self.reliability_dict = {
            'DB_SYSTEM': ['', ''],
            'HARDWARE': ['', ''],
            'HARDWARE_NEW': ['', ''],
            'HUMAN': ['', ''],
            'OS': ['', ''],
            'OS_NEW': ['', ''],
            'PORT': ['', ' '],
            'RTO': ['', ' '],
            'TOOLS': ['', ''],
            'ZLONGTIME': ['', '']}

    @staticmethod
    def cal_rate(dividends, divisor):
        if divisor == 0:
            return 0
        orgin_rate = int(dividends) / int(divisor) * 100
        if math.floor(orgin_rate) < 100 and math.ceil(orgin_rate) == 100:
            spot_index = str(orgin_rate).index('.')
            rate = float(str(orgin_rate)[:spot_index + 3:])
        elif math.floor(orgin_rate) == 100 and math.ceil(orgin_rate) == 100:
            rate = 100
        else:
            rate = round(orgin_rate, 2)

        return rate

    def get_data(self, model_table, v_feature):
        category_list = []
        cur = self.db.cursor()
        print('执行查询语句')
        sql_cmd = f"select model_name, sum(total_num) as total_num, " \
            f"sum(pass_num) as pass_num, sum(fail_num) as fail_num, " \
            f"sum(block_num) as block_num, min(start_time) as start_time, " \
            f"sum(run_time) as run_time " \
            f"from \"{model_table}\" " \
            f"group by model_name " \
            f"order by model_name;"
        cur.execute(sql_cmd)
        rows = cur.fetchall()
        for i in range(len(rows)):
            if 'func' in model_table and 'tier3' in model_table:
                prefix_dict_name = self.func_300_prefix
                dict_name = self.func_300_dict
            elif 'func' in model_table and 'tier2' in model_table:
                prefix_dict_name = self.func_200_prefix
                dict_name = self.func_200_dict
            elif 'func' in model_table and 'mas' in model_table:
                prefix_dict_name = self.func_master_prefix
                dict_name = self.func_master_dict
            else:
                prefix_dict_name = self.relia_prefix_dict
                dict_name = self.reliability_dict
            if 'func' in model_table:
                end = rows[i][0].index('_', 3)
                schd_name_prefix = rows[i][0][:end]
            else:
                if '_NEW_' not in rows[i][0]:
                    end = rows[i][0].rindex('_')
                else:
                    end = rows[i][0].rindex('_NEW_')
                schd_name_prefix = rows[i][0][:end]
            schd_list = prefix_dict_name.get(schd_name_prefix, ["", 0])
            category_list.append(schd_list[0])
            colspan = schd_list[1]
            develop_owner = dict_name.get(rows[i][0], ["", ""])[0]
            test_owner = dict_name.get(rows[i][0], ["", ""])[1]

            if i >= 1 and category_list[-1] == category_list[-2]:
                td_row = ''
            else:
                td_row = f'<td rowspan="{colspan}">{category_list[-1]}</td>'

            self.table_rows += f'''
                <tr bgcolor='#f5f5d6'>
                    {td_row}
                    <td>{rows[i][0]}</td>
                    <td>{rows[i][1]}</td>
                    <td>{self.cal_rate(rows[i][2], rows[i][1])}</td>
                    <td>{rows[i][2]}</td>
                    <td>{rows[i][3]}</td>
                    <td>{rows[i][4]}</td>
                    <td>{develop_owner}</td>
                    <td>{test_owner}</td>
                </tr> '''

        sql_cmd = f"select sum(total_num) as total_num, " \
            f"sum(pass_num) as pass_num, " \
            f"sum(fail_num) as fail_num, " \
            f"sum(block_num) as block_num " \
            f"from \"{model_table}\""
        cur.execute(sql_cmd)
        rows = cur.fetchall()
        self.table_rows += f'''<tr bgcolor='#d9ffcc'> 
                        <td colspan="2">合计</td>
                        <td>{rows[0][0]}</td> 
                        <td>{self.cal_rate(rows[0][1], rows[0][0])}</td>
                        <td>{rows[0][1]}</td> 
                        <td>{rows[0][2]}</td> 
                        <td>{rows[0][3]}</td>
                        <td></td>
                        <td></td> 
                    </tr> '''

        sql_cmd = f"set time zone 'PRC'; " \
            f"select * from \"ReportApp_dbverison\" " \
            f"where feature='{v_feature}' " \
            f"order by install_time desc limit 1;"
        cur.execute(sql_cmd)
        self.v_rows = cur.fetchall()


if __name__ == '__main__':
    Usage = "Usage:input as " \
            "'python3 auto_send_email.py feature tier' "
    if len(sys.argv) < 2:
        print(Usage)
        exit(0)
    # 取值范围[function, reliability]
    feature = sys.argv[1]
    tier = sys.argv[2]
    if feature == "function":
        if tier == '2':
            t_name_model = "ReportApp_tier2funcmodel"
            title_text = 'openGauss自动化CI功能2.0.0连跑结果'
            report_url = 'http://10.190.105.219:8000/tier2_200/'
            db_version_feature = 'tier2_function'
        elif tier == '3':
            t_name_model = "ReportApp_tier3funcmodel"
            title_text = 'openGauss自动化CI功能3.0.1连跑结果'
            report_url = 'http://10.190.105.219:8000/tier2_300/'
            db_version_feature = 'tier3_function'
        else:
            t_name_model = "ReportApp_masfuncmodel"
            title_text = 'openGauss自动化CI-Tier2-Master-功能连跑结果'
            report_url = 'http://10.190.105.219:8000/master/'
            db_version_feature = 'master_function'
    else:
        t_name_model = "ReportApp_reliamodel"
        title_text = 'openGauss自动化Tier3-可靠性CI连跑结果'
        report_url = 'http://10.190.105.219:8000/reliability/'
        db_version_feature = 'tier3_reliability'

    mail_host = "smtpextest.huawei.com"
    mail_user = "openGaussTest"
    mail_pass = "sua@99@wei"

    sender = 'openGaussTest@extest.com'
    to_receivers = ['guoweibiao2@huawei.com', 'baixiaoli@huawei.com',
                    'houshaolong@huawei.com', 'zoujialiang@h-partners.com',
                    'ningyali@huawei.com', 'zhangsuan93@huawei.com',
                    'peilinqian@huawei.com', 'qumin4@huawei.com',
                    'wangxiangxiang2@huawei.com', 'wanfang5@huawei.com',
                    'qiuying9@huawei.com', 'chengjiaqi2@huawei.com',
                    'yaokang9@huawei.com', 'wangke195@huawei.com',
                    'lizhuo44@huawei.com', 'xiteming@huawei.com',
                    'zhangao23@huawei.com', 'leiyanbin@huawei.com',
                    'lujianpei@huawei.com', 'xiaqi14@huawei.com',
                    'lixin477@h-partners.com', 'ganyang2@huawei.com',
                    'leissuai6@h-partners.com', 'yangruolan@h-partners.com',
                    'zhanghang97@h-partners.com', 'lihongji10@h-partners.com',
                    'chengyao25@h-partners.com', 'songjing20@h-partners.com']

    cc_receivers = ['jossua.chen@huawei.com', 'chendong76@huawei.com',
                    'chenxiaobin19@huawei.com', 'liyansong1@huawei.com',
                    'jikey.cai@huawei.com', 'f.fengwei@huawei.com',
                    'douxin5@huawei.com', 'zb.zhoubin@huawei.com',
                    'huzhengchao4@huawei.com', 'liuchun3@huawei.com',
                    'luozihao3@huawei.com', 'pengjiong1@huawei.com',
                    'wuyuecsuan@huawei.com', 'zhengxue6@huawei.com',
                    'xiongxiaojun2@huawei.com', 'xuemengen@huawei.com',
                    'zhangxubo1@huawei.com', 'zhangyaozhong1@huawei.com',
                    'zhangzhijing1@huawei.com']

    print('连接数据库')
    my_db = psycopg2.connect(database='autotest_report',
                             user='u_report',
                             password='sua@222*w',
                             host='10.244.69.227',
                             port=50517)
    AutoSendEmail = AutoSendEmail(my_db)
    AutoSendEmail.get_data(t_name_model, db_version_feature)

    print('断开数据库连接')
    mail_msg = f'''<!DOCTYPE html>
    <html>
    <head>
    <meta charset="UTF-8">
    
    <title>{title_text}</title>
    
    <style>
        table {{
            width: 100%;
            border-spacing: 0;
            border-collapse: collapse;
            table-layout: fixed;
            word-break: break-all;
        }}
    
        table th {{
            text-align: center;
        }}
    
        table td {{
            padding:10px 30px;
            text-align: center;
        }}
    </style>
    </head>
    
    <body>
        <p>本邮件由系统自动发出，无需回复！</p>	
        <p>各位同事，大家好，以下为openGauss自动化连跑信息:</p>
        <table id="t_SumModel" border="1" onload="func_load()"> 
            <tr> 
                <th colspan="9"><h1>{title_text}</h1></th> 
            </tr> 
            <tr bgcolor='#d9ffcc'> 
                <td colspan="2">数据库版本: </td>
                <td colspan="7">{AutoSendEmail.v_rows[0][-2]}</td>
            </tr>
            <tr bgcolor='#d9ffcc'> 
                <td colspan="2">安装时间: </td>
                <td colspan="7" stytle="text-align: left;">{
    AutoSendEmail.v_rows[0][-1]}</td>
            </tr>
            <tr bgcolor='#d9ffcc'> 
                <td stytle="width: '8%'">规格</td> 
                <td stytle="width: '20%'">测试套名</td> 
                <td stytle="width: '8%'">用例总数</td> 
                <td stytle="width: '8%'">成功率（%）</td>
                <td stytle="width: '6%'">成功</td>
                <td stytle="width: '6%'">失败</td>
                <td stytle="width: '6%'">阻塞</td>
                <td stytle="width: '19%'">开发责任人</td>
                <td stytle="width: '19%'">测试责任人</td>
            </tr> 
            {AutoSendEmail.table_rows}
        </table>
        <p>详细报告：<a href="{report_url}"> 点击跳转>> </a></p>
    </body>
    </html>
    '''

    receivers = to_receivers + cc_receivers
    message = MIMEText(mail_msg, 'html', 'utf-8')
    message['From'] = Header(sender, 'utf-8')
    message['To'] = Header(",".join(to_receivers))
    message['Cc'] = Header(",".join(cc_receivers))
    subject = title_text
    message['Subject'] = Header(subject, 'utf-8')
    try:
        smtp_obj = smtplib.SMTP()
        smtp_obj.connect(mail_host, 25)
        smtp_obj.login(mail_user, mail_pass)
        smtp_obj.sendmail(sender, receivers, message.as_string())
        print("邮件发送成功")
        smtp_obj.quit()
    except smtplib.SMTPException as e:
        print("Error: 无法发送邮件\n", e)
    my_db.close()
